﻿  using System;
  using System.Collections.Generic;
  using System.Text;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.Common;
  using System.Configuration;
  using System.Text.RegularExpressions;
  using System.Globalization;
  using System.Collections.Specialized;
  using System.Collections;

  //comm -- / <summary>
  //comm -- Provides interface to the abstract db layer 
  //comm -- / </summary>
  namespace nsDb
  {
    public class MetaGetter
    {

      private string _ConnectionString;
      public string ConnectionString
      {
        get { return _ConnectionString; }
        set { _ConnectionString = value; }
      } //eof property FieldName 

      public MetaGetter(string connectionStr)
      {
        this.ConnectionString = connectionStr;
      } //eof constructor 


      



      //GetProcedureMetaData(ref msg, ref ds, procName)
      public bool GetProcMetaDataListing(ref string msg, ref DataSet ds, string procName)
      {
        //debug Utils.Debugger.WriteIf("START --- GetProcedureMetaData");

        try
        {
          using (Database db = new Database(_ConnectionString))
          using (DbTransaction txn = db.BeginTransaction())
          {

            string sqlStringCommand = @"
                    SELECT PARAMETER_NAME as 'COLUMN_NAME', DATA_TYPE ,
                    CHARACTER_MAXIMUM_LENGTH AS 'MAX_LENGTH', IS_RESULT , 
                    PARAMETER_MODE  from INFORMATION_SCHEMA.PARAMETERS 
                    WHERE SPECIFIC_NAME='" + procName + "' AND 1=1";


            //debug Utils.Debugger.WriteIf ("CHECK --- GetProcedureMetaData --- The sql string is " + sqlStringCommand + "\n" );
            DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand);

            cmd.Transaction = txn;
            ds = db.ExecuteDataSet(cmd);

            txn.Commit();
            //take the @ away
            Utils.Data.DataSetGastArbeiter.ChopOffAts( ref msg , ref ds ) ;
            Utils.Data.DataSetGastArbeiter.CheckForUnfilledColumns(ref msg, ref ds);
            return true;
          } //eof using 
        } //eof try

        catch (Exception e)
        {
          System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
          string methodName = st.GetFrame(1).GetMethod().Name;
          Utils.Debugger.WriteIf(methodName + " --- The following Exception occured : \n" + e.Message);
          msg = "An error in the application occurred. Report the following error : \n " + methodName + e.Message;
          return false;
        } //eof catch
      } //eof method 

      public bool GetProcMeta(ref string msg, string domainName, string proc, ref DataSet ds)
      {

        string paramMeta = "procName";
        string procToRun = "proc_GetProcContentByName";
        string paramValue = proc;
        DataSet dsProcText = new DataSet();        //this is the ds holding the text of the procedure 
        nsDb.MetaDataBaseControl mdbc = new MetaDataBaseControl(_ConnectionString);
        //debug Utils.Debugger.WriteIf("the connection string is " + _ConnectionString);
        DbControllers.TableControl tco = new DbControllers.TableControl(_ConnectionString);



        //both the proc text retrieval and the proc parameter meta retrieval have to succeed !!!!
        if (tco.RunProcGetDsPassOneParam(ref msg, domainName, procToRun,
            ref dsProcText, paramValue, paramMeta) 
            && this.GetProcMetaDataListing(ref msg , ref ds , proc ))
        {

          //debug Utils.Debugger.DebugDataSet("from GetProcMeta --- dsProcText", ref dsProcText);
          //debug Utils.Debugger.DebugDataSet("from GetProcMeta --- ds ", ref ds);

          #region AddDescriptions

          int i = 0;
          DataTable dtProcText = dsProcText.Tables[0];

          foreach (DataRow drProcText in dtProcText.Rows)
          {
            string Line = drProcText[0].ToString().Trim();
            // we are looking for parameters and special comments before "AS"
            if (Line.ToLower().StartsWith("create"))
              continue;
            if (Line.ToLower().StartsWith("as"))
              continue;
            if (Line.Length == 0)
              continue;
            if (Line[0] == '@')
            {



              //ds.Tables[0].Rows.Add(dr);
              int j = Line.IndexOf("--");


              if (j >= 0)
              {
                //here are the comments per row
                ds.Tables[0].Rows[i]["DESCRIPTION"] = Line.Substring(j + 2, Line.Length - j - 2);
                Utils.Debugger.WriteIf("the description is " + ds.Tables[0].Rows[i]["DESCRIPTION"]);
                Utils.Debugger.WriteIf("The current column in ds is " + ds.Tables[0].Rows[i]["COLUMN_NAME"]);
                Line = Line.Substring(0, j);
              }




              j = Line.IndexOf("=");

              if (j >= 0)
              {
                // we have a default value to display
                string Default = Line.Substring(j + 1).Trim();
                Line = Line.Substring(0, j);
                if (Default[Default.Length - 1] == ',')
                  Default = Default.Substring(0, Default.Length - 1);
                if (Default[0] == '\'')
                  Default = Default.Substring(1);

                if (Default[Default.Length - 1] == '\'')
                  Default = Default.Substring(0, Default.Length - 1);
                Default = Default.Replace("''", "'");
                Utils.Debugger.WriteIf("The default is " + Default);
                //h["default"] = Default;
              } //eof if 


              // get rid of any extra spaces
              Line = Line.Replace("  ", " ");
              Line = Line.Replace("  ", " ");
              Line = Line.Replace("  ", " ");
              Line = Line.Replace("  ", " ");
              string[] Parts = Line.Split(new char[] { ' ' });
              //h["name"] = Parts[0];


              ds.Tables[0].Rows[i]["COLUMN_NAME"] =
                  Convert.ToString(Parts[0]).Trim().TrimStart(new char[] { '@' });

              string dataTypeAndMaxLength = Convert.ToString(Parts[1]).Trim();
              if (dataTypeAndMaxLength.Contains("("))
              {
                string[] p = dataTypeAndMaxLength.Split(new char[] { '(' });

                ds.Tables[0].Rows[i]["DATA_TYPE"] = Convert.ToString(p[0]).Trim();


                Utils.Debugger.WriteIf("p 1 is " + p[1].ToString());

                string maxLength = Convert.ToString(p[1]).Trim().TrimEnd(new char[] { ',' });
                Utils.Debugger.WriteIf("maxLength is " + maxLength);
                maxLength = Convert.ToString(maxLength).Trim().TrimEnd(new char[] { ')' });

                Utils.Debugger.WriteIf("maxLength is " + maxLength);
                ds.Tables[0].Rows[i]["MAX_LENGTH"] = maxLength;
              }
              else
              {
                ds.Tables[0].Rows[i]["DATA_TYPE"] = Convert.ToString(Parts[1]).Trim();
              }

              i++;  //increase count for parameters
              continue;
            } //eof if ( Line[0] == '@
          } //eof foreach drProcText in dt.Rows


          #endregion AddDescription

          return true;

        } //eof if
        else
        {
          Utils.Debugger.WriteIf("CHECK --- GetProcMeta -> RunProcGetDsPassOneParam -- return false ");
          return false;

        } //eof else 
      } //eof method GetProcMeta


      public bool GetProcMetaText ( ref string msg , string domainName , string proc , ref DataSet ds) 
      {
        //debug Utils.Debugger.WriteIf("START --- GetProcedureMetaData");

        try
        {
          using (Database db = new Database(_ConnectionString))
          using (DbTransaction txn = db.BeginTransaction())
          {

            string sqlStringCommand = @" exec sp_HelpText " + proc ;


            //debug Utils.Debugger.WriteIf ("CHECK --- GetProcMetaText --- The sql string is " + sqlStringCommand + "\n" );
            DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand);

            cmd.Transaction = txn;
            ds = db.ExecuteDataSet(cmd);
            Utils.Debugger.DebugDataSet("CHECK --- GetProcMetaText , ref ds ", ref ds);
            txn.Commit();
            return true;
          } //eof using 
        } //eof try

        catch (Exception e)
        {
          System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
          string methodName = st.GetFrame(1).GetMethod().Name;
          Utils.Debugger.WriteIf(methodName + " --- The following Exception occured : \n" + e.Message);
          msg = "An error in the application occurred. Report the following error : \n " + methodName + e.Message;
          return false;
        } //eof catch
      } //eof method 
      

    } //eof class 
 
} //eof namespace 
